--    Program: 		CAPARIMP_RECEIPT_SOURCE.sql
--    Description:  Import AR Receipt Source information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_batch_source_id		number;
                      		                 		
   cursor cur_pay_meth is
      select * from CRST_AR_BATCH_SOURCES_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_pay_meth loop  
   	 l_err_msg_tmp := '';
   	 
   	 select AR_BATCH_SOURCES_S.NEXTVAL
   	 into   l_batch_source_id
   	 from   dual;
   	 
   	 update CRST_AR_BATCH_SOURCES_ALL
   	 set    batch_source_id_n = l_batch_source_id,
   	        org_id_n = CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID)
   	 where  batch_source_id = cur.BATCH_SOURCE_ID
   	 and    org_id = cur.ORG_ID;
   	 
   	 begin 
   	   -- import data
       insert into AR_BATCH_SOURCES_ALL
       (  
         BATCH_SOURCE_ID                ,
         LAST_UPDATED_BY                ,
         LAST_UPDATE_DATE               ,
         LAST_UPDATE_LOGIN              ,
         CREATION_DATE                  ,
         CREATED_BY                     ,
         NAME                           ,
         TYPE                           ,
         START_DATE_ACTIVE              ,
         END_DATE_ACTIVE                ,
         DESCRIPTION                    ,
         DEFAULT_REMIT_BANK_ACCOUNT_ID  ,
         AUTO_BATCH_NUMBERING           ,
         LAST_BATCH_NUM                 ,
         ATTRIBUTE_CATEGORY             ,
         ATTRIBUTE1                     ,
         ATTRIBUTE2                     ,
         ATTRIBUTE3                     ,
         ATTRIBUTE4                     ,
         ATTRIBUTE5                     ,
         ATTRIBUTE6                     ,
         ATTRIBUTE7                     ,
         ATTRIBUTE8                     ,
         ATTRIBUTE9                     ,
         ATTRIBUTE10                    ,
         ATTRIBUTE11                    ,
         ATTRIBUTE12                    ,
         ATTRIBUTE13                    ,
         ATTRIBUTE14                    ,
         ATTRIBUTE15                    ,
         DEFAULT_RECEIPT_CLASS_ID       ,
         DEFAULT_RECEIPT_METHOD_ID      ,
         ORG_ID
       )
       values
       (
         l_batch_source_id                  ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.LAST_UPDATE_DATE               ,
         cur.LAST_UPDATE_LOGIN              ,
         cur.CREATION_DATE                  ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.NAME                           ,
         cur.TYPE                           ,
         cur.START_DATE_ACTIVE              ,
         cur.END_DATE_ACTIVE                ,
         cur.DESCRIPTION                    ,
         CAPBRIMP_MAPPING.GetBankACMapping(cur.DEFAULT_REMIT_BANK_ACCOUNT_ID),
         cur.AUTO_BATCH_NUMBERING           ,
         cur.LAST_BATCH_NUM                 ,
         cur.ATTRIBUTE_CATEGORY             ,
         cur.ATTRIBUTE1                     ,
         cur.ATTRIBUTE2                     ,
         cur.ATTRIBUTE3                     ,
         cur.ATTRIBUTE4                     ,
         cur.ATTRIBUTE5                     ,
         cur.ATTRIBUTE6                     ,
         cur.ATTRIBUTE7                     ,
         cur.ATTRIBUTE8                     ,
         cur.ATTRIBUTE9                     ,
         cur.ATTRIBUTE10                    ,
         cur.ATTRIBUTE11                    ,
         cur.ATTRIBUTE12                    ,
         cur.ATTRIBUTE13                    ,
         cur.ATTRIBUTE14                    ,
         cur.ATTRIBUTE15                     ,
         cur.DEFAULT_RECEIPT_CLASS_ID       ,
         cur.DEFAULT_RECEIPT_METHOD_ID      ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID)
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_RECEIPT_SOURCE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit